import java.util.List;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.SQLException;
import com.csvreader.CsvReader;
import common.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;
import wrapper.*;

/**
 * @author Or Kaplan
 * The importer implementation
 * insert new characters from unpacked fictional universes TSV folder
 */
public class UniverseImporter {
	private Connection conn; 
	private Map<String, InsertStatementWrapper> manyToManyStatements = new HashMap<String, InsertStatementWrapper>();
	private RelationTable[] allTables;
	private String currentWorkingFolder;
	private IReporter reporter;
	
	/**
	 * @param allTables - tables relation structure
	 * @param workingFolder - folder of exctracted fictional universe TSV dump
	 * @param reporter - error reporter
	 */
	public UniverseImporter(RelationTable[] allTables, String workingFolder, IReporter reporter) {
		this.currentWorkingFolder = workingFolder;
		this.allTables = allTables;
		this.reporter =  reporter;
	}

	/**
	 * creates many to many SQL statements and insert them into dictionary
	 * @throws SQLException
	 */
	private void InitManyToMany() throws SQLException {
		for (RelationTable table : GetTablesByType(RelationTable.TableType.ManyToManyTable)) {
			if (!manyToManyStatements.containsKey(table.getFields().get(1).getTableFieldName())) {
				String insertTempSql = String.format(Consts.insertManyToManyFormat,
						table.getTableName(), 
						table.getFields().get(1).getTableFieldName(), 
						table.getParentTable().getFields().get(0).getTableFieldName(), 
						table.getParentTable().getTableName(),
						table.getParentTable().getFields().get(1).getTableFieldName());
				
				String joinParsedSql = String.format(Consts.insertJoinManyFromat, 
						table.getTableName(), 
						table.getTableName(), 
						table.getTableName(), 
						table.getTableName(),
						table.getFields().get(1).getTableFieldName(),
						table.getTableName(),
						table.getTableName(),
						table.getFields().get(1).getTableFieldName());
				
				String deleteSql = String.format(Consts.deleteTempFormat, table.getTableName());
				
				manyToManyStatements.put(table.getFields().get(1).getTsvFieldName(), 
						new InsertStatementWrapper(table.getTableName(), insertTempSql, joinParsedSql, deleteSql));
			}
			InsertStatementWrapper wrapper = manyToManyStatements.get(table.getFields().get(1).getTsvFieldName());
			wrapper.prepareStatements(conn);
		}
	}
	
	/**
	 * Execute all many to many commands for batch
	 * @throws SQLException
	 */
	private void ExecuteManyToMany() throws SQLException {
		for (InsertStatementWrapper wrapper : manyToManyStatements.values()) {
			wrapper.executeCommand();
		}
	}

	/**
	 * build a insert wrapper for attribute table
	 * @param table
	 * @return the insert wrapper
	 * @throws SQLException
	 */
	private InsertStatementWrapper CreateInsertCommand(RelationTable table)
			throws SQLException {
		String tableName = table.getTableName();
		String idField = table.getFields().get(0).getTableFieldName();
		String nameField = table.getFields().get(1).getTableFieldName();
		String insert = String.format(Consts.insertFormat, tableName, idField, nameField);
		
		String join = FormatJoin(tableName, idField);
		String delete = String.format(Consts.deleteTempFormat, tableName);
		
		return new InsertStatementWrapper(tableName, insert, join, delete);
	}
	
	/**
	 * format the join sql command for all tables
	 * @param tableName
	 * @param idField
	 * @return the join sql
	 */
	private String FormatJoin(String tableName, String idField)
	{
		return String.format(Consts.insertJoinFromat, 
					tableName, 
					tableName,
					tableName,
					tableName,
					idField,
					tableName,
					idField);
	}
	
	/**
	 * get a new connection connection (remember to close me)
	 * @throws OpenConnectionException 
	 */
	private void openConnection() throws OpenConnectionException {
		try {
			conn = ConnectionPool.GetConnection();
		} catch (SQLException e) {
			throw new OpenConnectionException("Unable to connect - " + e.toString());
		} 
	}

	/**
	 * close the current connection
	 * @throws CloseConnectionException 
	 */
	private void closeConnection() throws CloseConnectionException {
		try {
			if (conn != null) 
				conn.close();
			conn = null;
		} catch (SQLException e) {
			Logger.GetLogger().LogException("Unable to close connection", e);
			throw new CloseConnectionException("Unable to close the connection - "
					+ e.toString());
		}

	}
	
	/**
	 * tries to insert record to batch by fields and tsv record - if fails reports warning
	 * @param insertWrapper
	 * @param fields
	 * @param reader
	 * @return is success
	 */
	private boolean TryInsert(InsertStatementWrapper insertWrapper, List<IFieldWrapper> fields, CsvReader reader)
	{
		if (!insertWrapper.InsertRecord(fields, reader)) {
			Logger.GetLogger().LogWarning("invalid field on inserting record to batch");
			reporter.ReportWarning("");
			return false;
		}
		return true;
	}
	
	/**
	 * tries to insert record to batch by fields and values - if fails reports warning
	 * @param insertWrapper
	 * @param fields
	 * @param reader
	 * @return is success
	 */
	private boolean TryInsert(InsertStatementWrapper insertWrapper, List<IFieldWrapper> fields, String[] values)
	{
		if (!insertWrapper.InsertRecord(fields, values)) {
			Logger.GetLogger().LogWarning("invalid field on inserting record to batch");
			reporter.ReportWarning("");
			return true;
		}
		return false;
	}
	
	/**
	 * insert all tsv records of attribute table in one batch
	 * @param table
	 * @throws SQLException
	 * @throws IOException
	 */
	private void InsertAttributeTable(RelationTable table) throws SQLException, IOException
	{
		InsertStatementWrapper insertWrapper = CreateInsertCommand(table);
		insertWrapper.prepareStatements(conn);
		CsvReader reader = null;
		try {
			reader = Utils.OpenReader(this.currentWorkingFolder, table.getFileName(), Arrays.asList(new RelationTable[] {table}));
			Logger.GetLogger().Log("adding table " + table.getTableName());
			
			int i = 0;
			while (reader.readRecord()) {
				TryInsert(insertWrapper, table.getFields(), reader);
				i++;
			}
	
			Logger.GetLogger().Log(i + " records about to being added");
		} finally {
			if (reader != null)
				reader.close();
		}
		insertWrapper.executeCommand();
	}
	
	/**
	 * insert records for all attribute tables
	 */
	private void InsertAttributeTables() 
	{
		for (RelationTable table : this.GetTablesByType(RelationTable.TableType.AttributeTable)) {
			try {
				InsertAttributeTable(table);
			} catch (SQLException e) {
				Logger.GetLogger().LogException("got Sql Exception During insert to table " + table.getTableName(), e);
				reporter.ReportError("");
			} catch (IOException e) {
				Logger.GetLogger().LogException("got Ie Exception During insert to table " + table.getFileName(), e);
				reporter.ReportError("");
			}
		}
	}
	
	/**
	 * get all tables from specific type
	 * @param type
	 * @return list of tables of the specified type
	 */
	private List<RelationTable> GetTablesByType(RelationTable.TableType type) {
		List<RelationTable> result = new ArrayList<RelationTable>();
		
		for (RelationTable relationTable : this.allTables) {
			if (relationTable.getType() == type)
				result.add(relationTable);
		}
		
		return result;
	}
	
	/**
	 * get the first table contains the tsv field on the index
	 * @param fieldName
	 * @param fieldIndex
	 * @return table with the specified tsv field
	 */
	private RelationTable GetTablesByTsvField(String fieldName, int fieldIndex) {
		for (RelationTable relationTable : this.allTables) {
			if (relationTable.getFields().size() > fieldIndex &&  
					relationTable.getFields().get(fieldIndex).getTsvFieldName().equals(fieldName))
				return relationTable;
		}
		return null;
	}
	
	/**
	 * insert batch of character for the character table and the many to many connecting table
	 * @param reader
	 * @param addedRecords
	 * @return has more records for next batch
	 * @throws SQLException
	 * @throws IOException
	 */
	private boolean InsertCharecterBatch(CsvReader reader, int[] addedRecords) throws SQLException, IOException
	{
		boolean hasMoreRecords = false; 
		
		RelationTable charTable = GetTablesByType(RelationTable.TableType.CharacterTable).get(0); 
		
		InsertStatementWrapper wrapper = new InsertStatementWrapper(charTable.getTableName(), 
					Consts.insertCharacterSql,
					FormatJoin(charTable.getTableName(), charTable.getFields().get(0).getTableFieldName()),
					String.format(Consts.deleteTempFormat, charTable.getTableName()));
		
		wrapper.prepareStatements(conn);
		
		InitManyToMany();
		hasMoreRecords = false;
		while (!hasMoreRecords && reader.readRecord()) {
			if (!TryInsert(wrapper, charTable.getFields(), reader))
				continue;

			// if have many to many tables set the insert statements
			if (manyToManyStatements != null) {
				for (String field : manyToManyStatements.keySet()) {
					// the DB is lower cased so use lower case to idetify the foreign keys
					// not using lower case made some bugs
					String[] otherTableKeys = reader.get(field).toLowerCase().trim()
							.split("\\s*,\\s*");
					
					InsertStatementWrapper manyWrapper = manyToManyStatements.get(field);
			        RelationTable manyTable = GetTablesByTsvField(field, 1);
			        
					for (String key : Utils.RemoveDuplicateString(otherTableKeys)) {
						if (!key.trim().isEmpty()) {
							String id = reader.get(manyTable.getFields().get(0).getTsvFieldName());
							TryInsert(manyWrapper, manyTable.getFields(), new String[] {id, key});
						}
					}
				}
			}
			addedRecords[0]++;
			if (addedRecords[0] % Consts.BatchMaxSize == 0)
			{
				hasMoreRecords = true;
			}
		}
		wrapper.executeCommand();
		ExecuteManyToMany();
			
		return hasMoreRecords;
	}
	
	/**
	 * insert records to character table and many to many table on batches
	 */
	private void InsertCharectersTable()
	{
		Logger.GetLogger().Log("adding table characters and connecting tables");
		CsvReader reader = null;
		try
		{
			int[] addedRecords = {0};
			List<RelationTable> tables = GetTablesByType(RelationTable.TableType.ManyToManyTable);
			tables.addAll(GetTablesByType(RelationTable.TableType.CharacterTable));
			reader = Utils.OpenReader(this.currentWorkingFolder, 
					GetTablesByType(RelationTable.TableType.CharacterTable).get(0).getFileName(), tables);
			
			while (InsertCharecterBatch(reader, addedRecords))
			{
				Logger.GetLogger().Log(String.format("loading characters %d have been added and counting...", addedRecords[0]));
			}
			
			Logger.GetLogger().Log(addedRecords[0] + " Charecters were scanned");
			Logger.GetLogger().Log("Finished adding table characters and connecting tables");
		} catch (SQLException e) {
			Logger.GetLogger().LogException("got Sql Exception During insert to charecter table", e);
			reporter.ReportError("");
		} catch (IOException e) {
			Logger.GetLogger().LogException("got IO Exception During insert to charecter table", e);
			reporter.ReportError("");
		}
		finally {
			if (reader != null)
				reader.close();
		}
	}
	
	/**
	 * creates all table's content 
	 * @throws CloseConnectionException
	 */
	public void InsertCharacters() throws CloseConnectionException {
		try {
			// get connection from pool
			openConnection();
			// insert the attribute tables
			InsertAttributeTables();
			// insert the characters and build the many to many tables
			InsertCharectersTable();
		} catch (OpenConnectionException e) {
			Logger.GetLogger().LogException("Failed to connect", e);
			reporter.ReportFatal("");
		} finally {
			closeConnection();
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Logger.SetLogger(new ConsoleLogger());
		if (args.length < 1 || args[0].isEmpty())
		{
			Logger.GetLogger().Log("USAGE: program_name csv_config_file");
			Logger.GetLogger().Log("please provide csv configuration file");
			return;
		}
		
		CsvReader reader = null;
		String url, user, pass, folder;
		try {
			reader = new CsvReader(args[0]);
			if (!reader.readHeaders() || !reader.readRecord())
				throw new IOException("unable to read configuration header and record");
			
			url = reader.get("url");
			user = reader.get("user");
			pass = reader.get("pass");
			folder = reader.get("folder");
		} catch (FileNotFoundException e1) {
			Logger.GetLogger().LogException("Invalid configuration file path", e1);
			return;
		} catch (IOException e) {
			Logger.GetLogger().LogException("invalid configuration content, should contain the fields: url,user,pass,folder", e);
			return;
		}
		finally {
			if (reader != null)
				reader.close();
		}
		
		FieldId idField = new FieldId("id", "id", true);
		FieldString nameField = new FieldString("name", "name", true);
		FieldId genderIdField = new FieldId("id", "gender_id", true);
		FieldString genderNameField = new FieldString("name", "gender", true);
		FieldFloat charHeightField = new FieldFloat("height", "height", false);
		FieldFloat charWeightField = new FieldFloat("weight", "weight", false);
		FieldString charGenderField = new FieldString("gender", "gender", false);
		
		RelationTable medicalConditionTable = new RelationTable("medical_condition", RelationTable.TableType.AttributeTable, "medical_condition_in_fiction", new IFieldWrapper[] {idField, nameField}, null);
		RelationTable occupationTable = new RelationTable("occupation", RelationTable.TableType.AttributeTable, "character_occupation", new IFieldWrapper[] {idField, nameField}, null);
		RelationTable powerTable = new RelationTable("power", RelationTable.TableType.AttributeTable, "character_powers", new IFieldWrapper[] {idField, nameField}, null);
		RelationTable rankTable = new RelationTable("rank", RelationTable.TableType.AttributeTable, "character_rank", new IFieldWrapper[] {idField, nameField}, null);
		RelationTable speciesTable = new RelationTable("species", RelationTable.TableType.AttributeTable, "character_species", new IFieldWrapper[] {idField, nameField}, null);
		RelationTable universeTable = new RelationTable("universe", RelationTable.TableType.AttributeTable, "fictional_universe", new IFieldWrapper[] {idField, nameField}, null);
		
		
		RelationTable[] allTables = new RelationTable[] {
				// attribute tables
				new RelationTable("gender", RelationTable.TableType.AttributeTable, "character_gender", new IFieldWrapper[] {genderIdField, genderNameField}, null),
				medicalConditionTable, occupationTable, powerTable, rankTable, speciesTable, universeTable,
				// characters table
				new RelationTable("characters", RelationTable.TableType.CharacterTable, "fictional_character", new IFieldWrapper[] {idField, nameField, charHeightField, charWeightField, charGenderField}, null),
				// many2many tables
				new RelationTable("character_medical_condition", RelationTable.TableType.ManyToManyTable, "fictional_character", new IFieldWrapper[] {idField, new FieldString("medical_conditions", "medical_condition_id", false)}, medicalConditionTable),
				new RelationTable("character_occupation", RelationTable.TableType.ManyToManyTable, "fictional_character", new IFieldWrapper[] {idField, new FieldString("occupation", "occupation_id", false)}, occupationTable),
				new RelationTable("character_power", RelationTable.TableType.ManyToManyTable, "fictional_character", new IFieldWrapper[] {idField, new FieldString("powers_or_abilities", "power_id", false)}, powerTable),
				new RelationTable("character_rank", RelationTable.TableType.ManyToManyTable, "fictional_character", new IFieldWrapper[] {idField, new FieldString("rank", "rank_id", false)}, rankTable),
				new RelationTable("character_species", RelationTable.TableType.ManyToManyTable, "fictional_character", new IFieldWrapper[] {idField, new FieldString("species", "species_id", false)}, speciesTable),
				new RelationTable("character_universe", RelationTable.TableType.ManyToManyTable, "fictional_character", new IFieldWrapper[] {idField, new FieldString("appears_in_these_fictional_universes", "universe_id", false)}, universeTable)
		};

		// init connection pool
		ConnectionPool.CreateInstance(url, user, pass);
		IReporter reporter = new SimpleReporter();
		UniverseImporter obj = new UniverseImporter(allTables, folder, reporter);
		
		try {
			obj.InsertCharacters();
		} catch (Exception e) {
			Logger.GetLogger().LogException("Got fatal exception on importer", e);
			reporter.ReportFatal("");
		}
		Logger.GetLogger().Log("Finished importer process with:");
		Logger.GetLogger().Log(reporter.GenerateReport());
	}
}
